﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;

namespace GroupProject
{

    public partial class frmSuppliers : Form
    {
        SqlConnection con;
        SqlCommand cmd;
        const string constr = @"server=.;database=TSQLFundamentals2008;uid=sa;pwd=123456";
        public frmSuppliers()
        {
            InitializeComponent();
            InitializeAOObject();
            loadData();
        }

        //Connection
        void InitializeAOObject()
        {
            con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["SqlStr"].ConnectionString; ;
            con.Open();
            cmd = new SqlCommand();
            cmd.Connection = con;
        }

        //Check if valid
        public bool valid()
        {
            bool valid = true;
            if (txtCompanyName.Text.Length == 0)
            {
                errorProvider1.SetError(txtCompanyName, "Company Name can't be null");
                valid = false;
            }
            if (txtContactName.Text.Length == 0)
            {
                errorProvider1.SetError(txtContactName, "Contact Name can't be null");
                valid = false;
            }
            if (txtContactTitle.Text.Length == 0)
            {
                errorProvider1.SetError(txtContactTitle, "Contact Title can't be null");
                valid = false;
            }
            if (txtAddress.Text.Length == 0)
            {
                errorProvider1.SetError(txtAddress, "Address can't be null");
                valid = false;
            }
            if (txtCity.Text.Length == 0)
            {
                errorProvider1.SetError(txtCity, "City can't be null");
                valid = false;
            }


            else
            {
                if (txtPostalCode.Text.Length != 5)
                {
                    errorProvider1.SetError(txtPostalCode, "Postal Code must have 5 number");
                    valid = false;
                    txtPostalCode.Focus();
                }
                try
                {
                    int poscode = int.Parse(txtPostalCode.Text);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message + "\n" + "Postal Code must be number", "Warning");
                    valid = false;
                    txtPostalCode.Focus();
                }
            }
            if (cboxCountry.Text.Length == 0)
            {
                errorProvider1.SetError(cboxCountry, "Country can't be null");
                valid = false;
            }
            if (mtxtPhone.Text.Length == 0)
            {
                errorProvider1.SetError(mtxtPhone, "Phone can't be null");
                valid = false;
            }



            if (valid == false)
            {
                return false;
            }
            return true;
        }

        //Load Data from Database
        void loadData()
        {
            try
            {
                InitializeAOObject();

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sup_getSupplier";
                SqlDataReader dr = cmd.ExecuteReader();
                dgvSuppliers.Rows.Clear();
                while (dr.Read())
                {
                    dgvSuppliers.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString(),
                        dr[5].ToString(), dr[6].ToString(), dr[7].ToString(), dr[8].ToString(), dr[9].ToString(), dr[10].ToString());
                }
                dr.Close();
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message);
            }
            finally
            {

                con.Close();
            }
        }
        //Insert, Update, Delete, Search
        #region 
        bool insertSuppliers()
        {
            bool result;
            try
            {
                InitializeAOObject();

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sup_insertSupplier";

                SqlParameter para = new SqlParameter("@companyName", SqlDbType.NVarChar);
                para.Size = 40;
                para.Value = txtCompanyName.Text.Trim();
                cmd.Parameters.Add(para);

                para = new SqlParameter("@contactName", SqlDbType.NVarChar);
                para.Size = 30;
                para.Value = txtContactName.Text.Trim();
                cmd.Parameters.Add(para);

                para = new SqlParameter("@contactTitle", SqlDbType.NVarChar);
                para.Size = 30;
                para.Value = txtContactTitle.Text.Trim();
                cmd.Parameters.Add(para);

                para = new SqlParameter("@address", SqlDbType.NVarChar);
                para.Size = 60;
                para.Value = txtAddress.Text.Trim();
                cmd.Parameters.Add(para);

                para = new SqlParameter("@city", SqlDbType.NVarChar);
                para.Size = 15;
                para.Value = txtCity.Text.Trim();
                cmd.Parameters.Add(para);

                para = new SqlParameter("@region", SqlDbType.NVarChar);
                para.Size = 15;
                para.Value = txtRegion.Text.Trim();
                cmd.Parameters.Add(para);

                para = new SqlParameter("@postalcode", SqlDbType.NVarChar);
                para.Size = 10;
                para.Value = txtPostalCode.Text.Trim();
                cmd.Parameters.Add(para);

                para = new SqlParameter("@country", SqlDbType.NVarChar);
                para.Size = 15;
                para.Value = cboxCountry.Text.Trim();
                cmd.Parameters.Add(para);

                para = new SqlParameter("@phone", SqlDbType.NVarChar);
                if (mtxtPhone.TextLength == 0)
                {
                    para.Value = DBNull.Value;
                }
                else
                {
                    para.Size = 24;
                    para.Value = mtxtPhone.Text.Trim();
                    cmd.Parameters.Add(para);
                }
                para = new SqlParameter("@fax", SqlDbType.NVarChar);
                para.Size = 24;
                para.Value = mtxtFax.Text.Trim();
                cmd.Parameters.Add(para);
                cmd.ExecuteNonQuery();
                result = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                result = false;
            }
            finally
            {
                con.Close();
            }
            if (result == false)
            {
                return false;
            }
            return true;
        }
        bool updateSuppliers()
        {
            bool result;
            try
            {
                InitializeAOObject();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sup_updateSupplier";

                SqlParameter para = new SqlParameter("@supplierID", SqlDbType.Int);
                para.Value = txtSupplierID.Text;
                cmd.Parameters.Add(para);

                para = new SqlParameter("@companyName", SqlDbType.NVarChar);
                para.Size = 40;
                para.Value = txtCompanyName.Text;
                cmd.Parameters.Add(para);

                para = new SqlParameter("@contactName", SqlDbType.NVarChar);
                para.Size = 30;
                para.Value = txtContactName.Text;
                cmd.Parameters.Add(para);

                para = new SqlParameter("@contactTitle", SqlDbType.NVarChar);
                para.Size = 30;
                para.Value = txtContactTitle.Text;
                cmd.Parameters.Add(para);

                para = new SqlParameter("@address", SqlDbType.NVarChar);
                para.Size = 60;
                para.Value = txtAddress.Text;
                cmd.Parameters.Add(para);

                para = new SqlParameter("@city", SqlDbType.NVarChar);
                para.Size = 15;
                para.Value = txtCity.Text;
                cmd.Parameters.Add(para);

                para = new SqlParameter("@region", SqlDbType.NVarChar);
                para.Size = 15;
                para.Value = txtRegion.Text;
                cmd.Parameters.Add(para);

                para = new SqlParameter("@postalcode", SqlDbType.NVarChar);
                para.Size = 10;
                para.Value = txtPostalCode.Text;
                cmd.Parameters.Add(para);

                para = new SqlParameter("@country", SqlDbType.NVarChar);
                para.Size = 15;
                para.Value = cboxCountry.Text;
                cmd.Parameters.Add(para);

                para = new SqlParameter("@phone", SqlDbType.NVarChar);
                para.Size = 24;
                para.Value = mtxtPhone.Text;
                cmd.Parameters.Add(para);

                para = new SqlParameter("@fax", SqlDbType.NVarChar);
                para.Size = 24;
                para.Value = mtxtFax.Text;
                cmd.Parameters.Add(para);

                cmd.ExecuteNonQuery();
                result = true;
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message);
                result = false;
            }
            finally
            {
                con.Close();
            }
            return result;
        }
        bool deleteSuppliers()
        {
            bool result = true;
            try
            {
                InitializeAOObject();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sup_deleteSupplier";

                SqlParameter para = new SqlParameter("@SupplierID", SqlDbType.Int);
                para.Value = Int32.Parse(txtSupplierID.Text);
                cmd.Parameters.Add(para);
                cmd.ExecuteNonQuery();
                result = true;
            }
            catch (Exception ex)
            {
                result = false;
                MessageBox.Show(ex.Message);

            }
            finally
            {
                con.Close();
            }
            return result;
        }
        void clear()
        {
            errorProvider1.Clear();
            txtSupplierID.Clear();
            txtCompanyName.Clear();
            txtContactName.Clear();
            txtContactTitle.Clear();
            txtAddress.Clear();
            txtCity.Clear();
            txtRegion.Clear();
            txtPostalCode.Clear();
            cboxCountry.SelectedIndex = -1;
            mtxtPhone.Clear();
            mtxtFax.Clear();
        }
        void searchbyID()
        {
            try
            {
                if (txtSearch.Text.Length == 0)
                {
                    MessageBox.Show("Please Enter an Key");
                    return;
                }
                InitializeAOObject();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sup_searchSupplierByID";

                SqlParameter para = new SqlParameter("@supplierID", SqlDbType.Int);
                para.Value = txtSearch.Text;

                cmd.Parameters.Add(para);
                String returnID = cmd.ExecuteScalar().ToString();

                cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sup_getSupplier";
                SqlDataReader dr = cmd.ExecuteReader();
                dgvSuppliers.Rows.Clear();
                while (dr.Read())
                {
                    if (dr[0].ToString().Equals(returnID))
                    {

                        dgvSuppliers.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString(), dr[5].ToString(),
                            dr[6].ToString(), dr[7].ToString(), dr[8].ToString(), dr[9].ToString(), dr[10].ToString());
                    }
                }
                dr.Close();
            }

            catch (Exception)
            {

                MessageBox.Show("ID " + txtSearch.Text + " is not exist");
                dgvSuppliers.Rows.Clear();
            }


            finally
            {
                con.Close();
            }



        }
        void searchbyCompanyName()
        {
            try
            {
                if (txtSearch.Text.Length == 0)
                {
                    MessageBox.Show("Please Enter an Key");
                    return;
                }
                InitializeAOObject();
                
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sup_searchSupplierByCompanyName";

                SqlParameter para = new SqlParameter("@companyName", SqlDbType.NVarChar);
                para.Size = 40;
                para.Value = txtSearch.Text;

                cmd.Parameters.Add(para);
                //String returnName = cmd.ExecuteScalar().ToString();
                SqlDataReader dr = cmd.ExecuteReader();
                dgvSuppliers.Rows.Clear();
                while (dr.Read())
                {

                    //if (dr[1].ToString().Equals(returnName))

                        dgvSuppliers.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString(), dr[5].ToString(),
                            dr[6].ToString(), dr[7].ToString(), dr[8].ToString(), dr[9].ToString(), dr[10].ToString());

                }
                dr.Close();
            }



            catch (Exception)
            {
                MessageBox.Show(txtSearch.Text + " is not exist in Data");
                dgvSuppliers.Rows.Clear();
            }
            finally
            {
                con.Close();
            }

        }
        
        #endregion


        private void btnList_Click(object sender, EventArgs e)
        {
            loadData();
        }

        private void btnRefresh_Click(object sender, EventArgs e)
        {
            clear();
        }

        private void frmSuppliers_Load(object sender, EventArgs e)
        {
            List<string> filterdata = new List<string>();
            filterdata.Add("--Choose search option--");
            filterdata.Add("ID");
            filterdata.Add("Company Name");
            cboxFilter.DataSource = filterdata;
        }

        private void btnInsert_Click(object sender, EventArgs e)
        {
            bool check;
            if (!valid())
            {
                return;
            }
            check = insertSuppliers();
            if (check == true)
            {
                MessageBox.Show("INSERT successfully!");
            }
            else
            {
                MessageBox.Show("INSERT failed!");
            }

            loadData();
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            bool check;
            if (valid())
            {
                try
                {
                    check = updateSuppliers();
                    if (check == true)
                    {
                        MessageBox.Show("UPDATE successfully!");
                        loadData();
                        clear();
                    }
                    else
                    {
                        MessageBox.Show("UPDATE failed!");
                        loadData();
                        clear();
                    }


                }
                catch (Exception ex)
                {

                    MessageBox.Show(ex.Message);
                }
            }
        }


        private void btnDelete_Click(object sender, EventArgs e)
        {
            bool del;
            if (MessageBox.Show("Are you sure to delete?", "WARNING!", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
            {

                del = deleteSuppliers();
                if (del == true)
                {
                    loadData();
                    clear();
                    MessageBox.Show("DELETE successfully!");
                }
                else
                {
                    loadData();
                    clear();
                    MessageBox.Show("DELETE failed!");
                }
            }
        }

        private void dgvSuppliers_SelectionChanged(object sender, EventArgs e)
        {
            if (dgvSuppliers.SelectedCells.Count > 1)
            {
                DataGridViewRow r = dgvSuppliers.SelectedRows[0];
                txtSupplierID.Text = r.Cells[0].Value.ToString();
                txtCompanyName.Text = r.Cells[1].Value.ToString();
                txtContactName.Text = r.Cells[2].Value.ToString();
                txtContactTitle.Text = r.Cells[3].Value.ToString();
                txtAddress.Text = r.Cells[4].Value.ToString();
                txtCity.Text = r.Cells[5].Value.ToString();
                txtRegion.Text = r.Cells[6].Value.ToString();
                txtPostalCode.Text = r.Cells[7].Value.ToString();
                cboxCountry.Text = r.Cells[8].Value.ToString();
                mtxtPhone.Text = r.Cells[9].Value.ToString();
                mtxtFax.Text = r.Cells[10].Value.ToString();
            }
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            if (cboxFilter.Text == "ID")
            {
                searchbyID();
            }
            if (cboxFilter.Text == "Company Name")
            {
                searchbyCompanyName();
            }
        }      
        
    }
}
